﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using IHISCETeim.BLL;

using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.HSSF.Util;

namespace IHISCETeim.Web.Export.FTradeModel
{
    public partial class FTradeCatheringListEasyUI2 : ExportPageBase
    {
        protected override string xlsTemplateFileName
        {
            get
            {
                return "应收帐款_财务部回款明细表.xls";
            }
        }
        protected override string SheetName
        {
            get
            {
                return "回款明细";
            }
        }
        //当前用户
        protected int UserId
        {
            get {
                return GetRequestValue<int>("uid", 0); 
            }
        }
        #region
        protected int Year
        {
            get
            {
                return GetRequestValue<int>("year", DateTime.Now.Year);
            }
        }
        protected int Month
        {
            get
            {
                return GetRequestValue<int>("Month", DateTime.Now.Month);
            }
        }
        /// <summary>
        /// 所选月份第一天
        /// </summary>
        protected DateTime FDate
        {
            get
            {
                return Convert.ToDateTime(string.Format("{0}-{1}-{2}", Year, Month, 1));
            }
        }
        /// <summary>
        /// 所选月份最后一天
        /// </summary>
        protected DateTime TDate
        {
            get
            {
                return FDate.AddMonths(1).AddDays(-1);
            }
        }

        protected DateTime YearFDate
        {
            get
            {
                return Convert.ToDateTime(FDate.Year.ToString() + "-01-01");
            }
        }
        protected DateTime YearTDate
        {
            get
            {
                return Convert.ToDateTime(FDate.Year.ToString() + "-12-31");
            }
        }
        #endregion
        /// 获取各个列值 
        /// </summary>
        /// <param name="JingXiaoShang"></param>
        /// <param name="ColumnName"></param>
        /// <returns></returns>
        private Double GetVal(string JingXiaoShang, string ColumnName)
        {
            object tempval = DBNull.Value;

            string sqlwhere = " SourceTag='人工' ";
            string sqlwhere_KuanXiangLeiXing = "";
           
            string[] cNameArr = ColumnName.Split(new char[] { '_' });
            string _KuanXiangLeiXing = cNameArr[0];
            string _fdate = cNameArr[1];
            string _tdate = cNameArr[2];

            if (_KuanXiangLeiXing == "其他")
            {
                sqlwhere_KuanXiangLeiXing = " KuanXiangLeiXing not in('电汇','汇票','融资','调帐') ";
            }
            else if (_KuanXiangLeiXing == "合计")
            {
                sqlwhere_KuanXiangLeiXing = " KuanXiangLeiXing<>'调帐' ";
            }
            else
            {
                sqlwhere_KuanXiangLeiXing = string.Format("  KuanXiangLeiXing='{0}'", _KuanXiangLeiXing);
            }
          
            //行合计
            if (JingXiaoShang == "合计:")
            {
                sqlwhere += string.Format(" and ShoukuanRiQi between '{0}' and '{1}'", _fdate, _tdate);
                sqlwhere += string.Format(" and {0}", sqlwhere_KuanXiangLeiXing);
                //营业经理权限处理
                string agentsqltemp = FTradeAgentPowerSqlWhere(UserId);

                sqlwhere += string.IsNullOrEmpty(agentsqltemp) ? "" : string.Format(" and JingXiaoShang in ({0})", agentsqltemp);

                 tempval = FTradeCatheringController.GetHuKunJinE(sqlwhere);
                return Convert.ToDouble(tempval == DBNull.Value ? 0 : tempval);
            }

            //列合计
            //if (_KuanXiangLeiXing == "合计")
            //{
            //    sqlwhere += string.Format(" ShoukuanRiQi between '{0}' and '{1}'", _fdate, _tdate);
            //    sqlwhere += string.Format(" and JingXiaoShang='{0}'", JingXiaoShang);
            //    sqlwhere += string.Format(" and KuanXiangLeiXing<>'调帐'", JingXiaoShang);

            //    tempval = FTradeCatheringController.GetHuKunJinE(sqlwhere);
            //    return Convert.ToDouble(tempval == DBNull.Value ? 0 : tempval);
            //}

            sqlwhere += string.Format(" and ShoukuanRiQi between '{0}' and '{1}'", _fdate, _tdate);
            sqlwhere += string.Format(" and JingXiaoShang='{0}'", JingXiaoShang);
            sqlwhere += string.Format(" and {0}", sqlwhere_KuanXiangLeiXing);

            // object XX = FTradeCatheringController.GetHuKunJinE(sqlwhere);

             tempval = FTradeCatheringController.GetHuKunJinE(sqlwhere);
            return Convert.ToDouble(tempval == DBNull.Value ? 0 : tempval);
        }


        protected void AddTitle(ISheet sheet1)
        {
            //1.写入帐龄标题列
            int titleRowColumnIndex = 1;         

            DateTime everyday = FDate;
            DateTime weekStateDay = FDate;
            int weekcount = 1;//周计数
            string returnstr = "";
            while (everyday <= TDate)
            {
                if ((everyday.DayOfWeek == DayOfWeek.Sunday) || (everyday == TDate))
                {
                   //sheet1.GetRow(1).GetCell(titleRowColumnIndex).SetCellValue(string.Format("第 {0} 周({1})", weekcount, weekStateDay.ToString("MM.dd") + "-" + everyday.ToString("MM.dd")));
                    sheet1.GetRow(1).GetCell(titleRowColumnIndex).SetCellValue(string.Format("第 {0} 周({1})", weekcount, weekStateDay.ToString("MM.dd") + "-" + everyday.ToString("MM.dd")));

                    sheet1.GetRow(2).GetCell(titleRowColumnIndex + 0).SetCellValue("电汇");
                    sheet1.GetRow(2).GetCell(titleRowColumnIndex + 1).SetCellValue("汇票");
                    sheet1.GetRow(2).GetCell(titleRowColumnIndex + 2).SetCellValue("融资");
                    sheet1.GetRow(2).GetCell(titleRowColumnIndex + 3).SetCellValue("其他");
                    sheet1.GetRow(2).GetCell(titleRowColumnIndex + 4).SetCellValue("合计");

                    titleRowColumnIndex = titleRowColumnIndex+5;

                    weekStateDay = everyday.AddDays(1);
                    weekcount++;
                }
                everyday = everyday.AddDays(1);
            }

            sheet1.GetRow(1).GetCell(titleRowColumnIndex).SetCellValue(string.Format("{0}", " 本月合计 "));

            sheet1.GetRow(2).CreateCell(titleRowColumnIndex + 0).SetCellValue("电汇");
            sheet1.GetRow(2).GetCell(titleRowColumnIndex + 1).SetCellValue("汇票");
            sheet1.GetRow(2).GetCell(titleRowColumnIndex + 2).SetCellValue("融资");
            sheet1.GetRow(2).GetCell(titleRowColumnIndex + 3).SetCellValue("其他");
            sheet1.GetRow(2).GetCell(titleRowColumnIndex + 4).SetCellValue("合计");

            titleRowColumnIndex = titleRowColumnIndex + 5;

            sheet1.GetRow(1).GetCell(titleRowColumnIndex).SetCellValue(string.Format("{0}", " 本年累计 "));

            sheet1.GetRow(2).GetCell(titleRowColumnIndex + 0).SetCellValue("电汇");
            sheet1.GetRow(2).GetCell(titleRowColumnIndex + 1).SetCellValue("汇票");
            sheet1.GetRow(2).GetCell(titleRowColumnIndex + 2).SetCellValue("融资");
            sheet1.GetRow(2).GetCell(titleRowColumnIndex + 3).SetCellValue("其他");
            sheet1.GetRow(2).GetCell(titleRowColumnIndex + 4).SetCellValue("合计");
            //titleRowColumnIndex = titleRowColumnIndex + 5;

            
        }

        protected void AddRowDate(ISheet sheet1)
        {
            string agentsqltemp = FTradeAgentPowerSqlWhere(UserId);
            //
            DataTable JingXiaoShangDT = FTradeCatheringController.GetHuKunAgentList(agentsqltemp, YearFDate.ToString("yyyy-MM-dd 00:00:00"), YearTDate.ToString("yyyy-MM-dd 23:59:59"));           

            int writerowindex = 3;

            foreach (DataRow dr in JingXiaoShangDT.Rows)
            {
                string JingXiaoShang = dr["JingXiaoShang"].ToString();
                AddRow(sheet1, writerowindex, JingXiaoShang);
                writerowindex++;
               
            }

            AddRow(sheet1, writerowindex, "合计:");
        }


        private void AddRow(ISheet sheet1, int writerowindex, string JingXiaoShang)
        {
            DateTime everyday = FDate;
            DateTime weekStateDay = FDate;
            int weekcount = 1;//周计数
            int writecellindex = 1;
            HSSFRow writerow = (HSSFRow)sheet1.CreateRow(writerowindex);
            //string JingXiaoShang = dr["JingXiaoShang"].ToString();

            writerow.CreateCell(0).SetCellValue(JingXiaoShang);

            while (everyday <= TDate)
            {
                if ((everyday.DayOfWeek == DayOfWeek.Sunday) || (everyday == TDate))
                {
                    writerow.CreateCell(writecellindex++).SetCellValue(GetVal(JingXiaoShang, string.Format("{0}_{1}_{2}", "电汇", weekStateDay.ToString("yyyy-MM-dd"), everyday.ToString("yyyy-MM-dd"))));
                    writerow.CreateCell(writecellindex++).SetCellValue(GetVal(JingXiaoShang, string.Format("{0}_{1}_{2}", "汇票", weekStateDay.ToString("yyyy-MM-dd"), everyday.ToString("yyyy-MM-dd"))));
                    writerow.CreateCell(writecellindex++).SetCellValue(GetVal(JingXiaoShang, string.Format("{0}_{1}_{2}", "融资", weekStateDay.ToString("yyyy-MM-dd"), everyday.ToString("yyyy-MM-dd"))));
                    writerow.CreateCell(writecellindex++).SetCellValue(GetVal(JingXiaoShang, string.Format("{0}_{1}_{2}", "其他", weekStateDay.ToString("yyyy-MM-dd"), everyday.ToString("yyyy-MM-dd"))));
                    writerow.CreateCell(writecellindex++).SetCellValue(GetVal(JingXiaoShang, string.Format("{0}_{1}_{2}", "合计", weekStateDay.ToString("yyyy-MM-dd"), everyday.ToString("yyyy-MM-dd"))));

                    weekStateDay = everyday.AddDays(1);
                    weekcount++;
                }
                everyday = everyday.AddDays(1);
            }

            //本月合计
            writerow.CreateCell(writecellindex++).SetCellValue(GetVal(JingXiaoShang, string.Format("{0}_{1}_{2}", "电汇", FDate.ToString("yyyy-MM-dd"), TDate.ToString("yyyy-MM-dd"))));
            writerow.CreateCell(writecellindex++).SetCellValue(GetVal(JingXiaoShang, string.Format("{0}_{1}_{2}", "汇票", FDate.ToString("yyyy-MM-dd"), TDate.ToString("yyyy-MM-dd"))));
            writerow.CreateCell(writecellindex++).SetCellValue(GetVal(JingXiaoShang, string.Format("{0}_{1}_{2}", "融资", FDate.ToString("yyyy-MM-dd"), TDate.ToString("yyyy-MM-dd"))));
            writerow.CreateCell(writecellindex++).SetCellValue(GetVal(JingXiaoShang, string.Format("{0}_{1}_{2}", "其他", FDate.ToString("yyyy-MM-dd"), TDate.ToString("yyyy-MM-dd"))));
            writerow.CreateCell(writecellindex++).SetCellValue(GetVal(JingXiaoShang, string.Format("{0}_{1}_{2}", "合计", FDate.ToString("yyyy-MM-dd"), TDate.ToString("yyyy-MM-dd"))));

            //本年合计
            writerow.CreateCell(writecellindex++).SetCellValue(GetVal(JingXiaoShang, string.Format("{0}_{1}_{2}", "电汇", YearFDate.ToString("yyyy-MM-dd"), YearTDate.ToString("yyyy-MM-dd"))));
            writerow.CreateCell(writecellindex++).SetCellValue(GetVal(JingXiaoShang, string.Format("{0}_{1}_{2}", "汇票", YearFDate.ToString("yyyy-MM-dd"), YearTDate.ToString("yyyy-MM-dd"))));
            writerow.CreateCell(writecellindex++).SetCellValue(GetVal(JingXiaoShang, string.Format("{0}_{1}_{2}", "融资", YearFDate.ToString("yyyy-MM-dd"), YearTDate.ToString("yyyy-MM-dd"))));
            writerow.CreateCell(writecellindex++).SetCellValue(GetVal(JingXiaoShang, string.Format("{0}_{1}_{2}", "其他", YearFDate.ToString("yyyy-MM-dd"), YearTDate.ToString("yyyy-MM-dd"))));
            writerow.CreateCell(writecellindex++).SetCellValue(GetVal(JingXiaoShang, string.Format("{0}_{1}_{2}", "合计", YearFDate.ToString("yyyy-MM-dd"), YearTDate.ToString("yyyy-MM-dd"))));

            //writerowindex++;
        }


        protected void Page_Load(object sender, EventArgs e)
        {
            //初始化
            InitializeWorkbook();
            ISheet sheet1 = hssfworkbook.GetSheet(SheetName);
            //写入标题
            AddTitle(sheet1);

            AddRowDate(sheet1);

            hssfworkbook.SetSheetName(0, string.Format("{0}年{1}月回款", Year, Month));
           // sheet1.SheetName =string.Format( "{0}年{1}月回款",Year,Month);
            //写入
            WriteToFile();
        }

        protected override string GetXlsFileName(string oldFileName)
        {
            //return base.GetXlsFileName(oldFileName);

            return string.Format("{0}{1}.xls", "回款明细表", DateTime.Now.ToString("yyyy-MM-dd")).Trim();
        }
    }
}